package com.qf.dao.impl;


import com.qf.dao.GoodsDao;
import com.qf.domain.Goods;
import com.qf.domain.GoodsType;
import com.qf.domain.User;
import com.qf.utils.DataSourceUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * projectName:myxiaomi
 * author:liuXin
 * time:2024/4/16 11:55
 * description:
 */
public class GoodsDaoImpl implements GoodsDao {
    private QueryRunner queryRunner=new QueryRunner(DataSourceUtil.getDataSource());
    @Override
    public long getCount(String where, List<Object> params) {
        String sql=" SELECT COUNT(*) FROM db_shopping.`tb_goods` "+ where;
        try {
            return queryRunner.query(sql,new ScalarHandler<>(),params.toArray());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<Goods> selectByPage(int page_num, int page_size, String where, List<Object> params) {
        params.add((page_num-1)*page_size);
        params.add(page_size);
        String sql=" SELECT * FROM db_shopping.`tb_goods` "+where+" ORDER BY id LIMIT ?,? ";
        try {
            return queryRunner.query(sql,new BeanListHandler<>(Goods.class),params.toArray());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Goods selectById(Integer gid) {
        String sql=" SELECT * FROM db_shopping.`tb_goods` WHERE id=? ";
        try {
            return queryRunner.query(sql,new BeanHandler<>(Goods.class),gid);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public void insert(Goods goods) {
        String sql = " insert into db_shopping.tb_goods(name, pubdate, picture, star,price, intro, typeid) values (?,?,?,?,?,?,?)";
        Object[] params={goods.getName(),goods.getPubdate(),goods.getPicture(),goods.getStar(),goods.getPrice(),goods.getIntro(),goods.getTypeid()};
        try {
            queryRunner.update(sql,params);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<GoodsType> select() {
        String sql = "  select * from db_shopping.tb_goods_type  ";
        try {
            return queryRunner.query(sql,new BeanListHandler<>(GoodsType.class));
        } catch (SQLException throwables) {
            throw new RuntimeException(throwables);
        }
    }

    @Override
    public List<User> selectUser() {
        String sql = " select * from  db_shopping.tb_user where role not in(2) ";
        try {
            return queryRunner.query(sql,new BeanListHandler<>(User.class));
        } catch (SQLException throwables) {
            throw new RuntimeException(throwables);
        }
    }

    @Override
    public void add(GoodsType goodsType) {
        String sql = " insert into db_shopping.tb_goods_type(name, level, parent) values (?,?,?)";
        Object[] params={goodsType.getName(),goodsType.getLevel(),goodsType.getParent()};
        try {
            queryRunner.update(sql,params);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<Goods> selectgoods() {
        String sql = " select * from  db_shopping.tb_goods  ";
        try {
            return queryRunner.query(sql,new BeanListHandler<>(Goods.class));
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public void deleteGoods(int id) {
        String sql = " delete from db_shopping.tb_goods where id=? ";
        try {
            queryRunner.update(sql,id);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<User> selectinvalidUser() {
        String sql = " select * from  db_shopping.tb_user where role=? ";
        try {
            return queryRunner.query(sql,new BeanListHandler<>(User.class),2);
        } catch (SQLException throwables) {
            throw new RuntimeException(throwables);
        }
    }

    @Override
    public void update(Goods goods) {
        String sql ="update db_shopping.tb_goods set name=?, pubdate=?, picture=? ,price=?  ,star=?  ,intro=? ,typeid=? where id=?";
        Object []param={goods.getName(),goods.getPubdate(),goods.getPicture(),goods.getPrice(),goods.getStar(),goods.getIntro(),goods.getTypeid(),goods.getId()};
        try {
            queryRunner.update(sql,param);
        } catch (SQLException throwables) {
            throw new RuntimeException(throwables);
        }
    }

    @Override
    public List<Goods> findAll() {
        try {
            return queryRunner.query("select * from db_shopping.tb_goods",new BeanListHandler<>(Goods.class));
        } catch (SQLException throwables) {
            throw new RuntimeException(throwables);
        }
    }
}
